﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Global.Class.Library;
using DataAccessLayer.ERmgrUIPTableAdapters;

namespace DataAccessLayer
{
    public class CommonDatabaseMethod
    {
        public CommonDatabaseMethod()
        {
            try
            {
                if (GlobalVariables.shouldRestoreProcedure) RestoreProcedure();
            }
            catch (Exception exception)
            {
                throw exception;
            }

        }




        


        /// <summary>
        /// Get a spcefic ItemCategory
        /// </summary>
        /// <param name="itemCategoryID"></param>
        /// <param name="itemCategoryDescription"></param>
        /// <returns></returns>
        public static ERmgrUIP.SPGetItemCategoryRow GetItemCategory(int itemCategoryID, string itemCategoryDescription)
        {
            ERmgrUIP.SPGetItemCategoryDataTable itemCategoryDataTable = CommonDatabaseMethod.SPGetItemCategoryTableAdapter.GetData(itemCategoryID, itemCategoryDescription);

            if (itemCategoryDataTable != null && itemCategoryDataTable.Rows.Count > 0) return (ERmgrUIP.SPGetItemCategoryRow)itemCategoryDataTable.Rows[0]; else return null;
        }

        static SPGetItemCategoryTableAdapter spGetItemCategoryTableAdapter;
        private static SPGetItemCategoryTableAdapter SPGetItemCategoryTableAdapter
        {
            get
            {
                if (spGetItemCategoryTableAdapter == null) spGetItemCategoryTableAdapter = new SPGetItemCategoryTableAdapter();
                return spGetItemCategoryTableAdapter;
            }
        }


        /// <summary>
        /// Get a spcefic ItemType
        /// </summary>
        /// <param name="itemTypeID"></param>
        /// <param name="itemTypeDescription"></param>
        /// <returns></returns>
        public static ERmgrUIP.SPGetItemTypeRow GetItemType(int itemTypeID, string itemTypeDescription)
        {
            ERmgrUIP.SPGetItemTypeDataTable itemTypeDataTable = CommonDatabaseMethod.SPGetItemTypeTableAdapter.GetData(itemTypeID, itemTypeDescription);

            if (itemTypeDataTable != null && itemTypeDataTable.Rows.Count > 0) return (ERmgrUIP.SPGetItemTypeRow)itemTypeDataTable.Rows[0]; else return null;
        }

        static SPGetItemTypeTableAdapter spGetItemTypeTableAdapter;
        private static SPGetItemTypeTableAdapter SPGetItemTypeTableAdapter
        {
            get
            {
                if (spGetItemTypeTableAdapter == null) spGetItemTypeTableAdapter = new SPGetItemTypeTableAdapter();
                return spGetItemTypeTableAdapter;
            }
        }


        /// <summary>
        /// Get a spcefic ItemCommodity
        /// </summary>
        /// <param name="commodityID"></param>
        /// <param name="commodityDescription"></param>
        /// <returns></returns>
        public static ERmgrUIP.SPGetItemCommodityRow GetItemCommodity(int commodityID, string commodityDescription)
        {
            ERmgrUIP.SPGetItemCommodityDataTable itemCommodityDataTable = CommonDatabaseMethod.SPGetItemCommodityTableAdapter.GetData(commodityID, commodityDescription);

            if (itemCommodityDataTable != null && itemCommodityDataTable.Rows.Count > 0) return (ERmgrUIP.SPGetItemCommodityRow)itemCommodityDataTable.Rows[0]; else return null;
        }

        static SPGetItemCommodityTableAdapter spGetItemCommodityTableAdapter;
        private static SPGetItemCommodityTableAdapter SPGetItemCommodityTableAdapter
        {
            get
            {
                if (spGetItemCommodityTableAdapter == null) spGetItemCommodityTableAdapter = new SPGetItemCommodityTableAdapter();
                return spGetItemCommodityTableAdapter;
            }
        }



        /// <summary>
        /// Get a spcefic AddressArea
        /// </summary>
        /// <param name="addressAreaID"></param>
        /// <param name="addressAreaDescription"></param>
        /// <returns></returns>
        public static ERmgrUIP.SPGetAddressAreaRow GetAddressArea(int addressAreaID, string addressAreaDescription)
        {
            ERmgrUIP.SPGetAddressAreaDataTable addressAreaDataTable = CommonDatabaseMethod.SPGetAddressAreaTableAdapter.GetData(addressAreaID, addressAreaDescription);

            if (addressAreaDataTable != null && addressAreaDataTable.Rows.Count > 0) return (ERmgrUIP.SPGetAddressAreaRow)addressAreaDataTable.Rows[0]; else return null;
        }

        static SPGetAddressAreaTableAdapter spGetAddressAreaTableAdapter;
        private static SPGetAddressAreaTableAdapter SPGetAddressAreaTableAdapter
        {
            get
            {
                if (spGetAddressAreaTableAdapter == null) spGetAddressAreaTableAdapter = new SPGetAddressAreaTableAdapter();
                return spGetAddressAreaTableAdapter;
            }
        }



        /// <summary>
        /// Get a spcefic CustomerChannel
        /// </summary>
        /// <param name="customerChannelID"></param>
        /// <param name="customerChannelDescription"></param>
        /// <returns></returns>
        public static ERmgrUIP.SPGetCustomerChannelRow GetCustomerChannel(int customerChannelID, string customerChannelDescription, bool primaryChannelOnly)
        {
            ERmgrUIP.SPGetCustomerChannelDataTable customerChannelDataTable = CommonDatabaseMethod.SPGetCustomerChannelTableAdapter.GetData(customerChannelID, customerChannelDescription, primaryChannelOnly);

            if (customerChannelDataTable != null && customerChannelDataTable.Rows.Count > 0) return (ERmgrUIP.SPGetCustomerChannelRow)customerChannelDataTable.Rows[0]; else return null;
        }

        static SPGetCustomerChannelTableAdapter spGetCustomerChannelTableAdapter;
        private static SPGetCustomerChannelTableAdapter SPGetCustomerChannelTableAdapter
        {
            get
            {
                if (spGetCustomerChannelTableAdapter == null) spGetCustomerChannelTableAdapter = new SPGetCustomerChannelTableAdapter();
                return spGetCustomerChannelTableAdapter;
            }
        }

        /// <summary>
        /// Get a spcefic CustomerCategory
        /// </summary>
        /// <param name="customerCategoryID"></param>
        /// <param name="customerCategoryDescription"></param>
        /// <returns></returns>
        public static ERmgrUIP.SPGetCustomerCategoryRow GetCustomerCategory(int customerCategoryID, string customerCategoryDescription, bool primaryChannelOnly)
        {
            ERmgrUIP.SPGetCustomerCategoryDataTable customerCategoryDataTable = CommonDatabaseMethod.SPGetCustomerCategoryTableAdapter.GetData(customerCategoryID, customerCategoryDescription, primaryChannelOnly);

            if (customerCategoryDataTable != null && customerCategoryDataTable.Rows.Count > 0) return (ERmgrUIP.SPGetCustomerCategoryRow)customerCategoryDataTable.Rows[0]; else return null;
        }

        static SPGetCustomerCategoryTableAdapter spGetCustomerCategoryTableAdapter;
        private static SPGetCustomerCategoryTableAdapter SPGetCustomerCategoryTableAdapter
        {
            get
            {
                if (spGetCustomerCategoryTableAdapter == null) spGetCustomerCategoryTableAdapter = new SPGetCustomerCategoryTableAdapter();
                return spGetCustomerCategoryTableAdapter;
            }
        }

        /// <summary>
        /// Get a spcefic CustomerType
        /// </summary>
        /// <param name="customerTypeID"></param>
        /// <param name="customerTypeDescription"></param>
        /// <returns></returns>
        public static ERmgrUIP.SPGetCustomerTypeRow GetCustomerType(int customerTypeID, string customerTypeDescription, bool primaryChannelOnly)
        {
            ERmgrUIP.SPGetCustomerTypeDataTable customerTypeDataTable = CommonDatabaseMethod.SPGetCustomerTypeTableAdapter.GetData(customerTypeID, customerTypeDescription, primaryChannelOnly);

            if (customerTypeDataTable != null && customerTypeDataTable.Rows.Count > 0) return (ERmgrUIP.SPGetCustomerTypeRow)customerTypeDataTable.Rows[0]; else return null;
        }

        static SPGetCustomerTypeTableAdapter spGetCustomerTypeTableAdapter;
        private static SPGetCustomerTypeTableAdapter SPGetCustomerTypeTableAdapter
        {
            get
            {
                if (spGetCustomerTypeTableAdapter == null) spGetCustomerTypeTableAdapter = new SPGetCustomerTypeTableAdapter();
                return spGetCustomerTypeTableAdapter;
            }
        }


        /// <summary>
        /// Get a spcefic CustomerName
        /// </summary>
        /// <param name="customerID"></param>
        /// <param name="customerNameDescription"></param>
        /// <returns></returns>
        public static ERmgrUIP.SPGetCustomerNameRow GetCustomerName(int customerID, string customerNameDescription)
        {
            ERmgrUIP.SPGetCustomerNameDataTable customerNameDataTable = CommonDatabaseMethod.SPGetCustomerNameTableAdapter.GetData(customerID, customerNameDescription);

            if (customerNameDataTable != null && customerNameDataTable.Rows.Count > 0) return (ERmgrUIP.SPGetCustomerNameRow)customerNameDataTable.Rows[0]; else return null;
        }

        static SPGetCustomerNameTableAdapter spGetCustomerNameTableAdapter;
        private static SPGetCustomerNameTableAdapter SPGetCustomerNameTableAdapter
        {
            get
            {
                if (spGetCustomerNameTableAdapter == null) spGetCustomerNameTableAdapter = new SPGetCustomerNameTableAdapter();
                return spGetCustomerNameTableAdapter;
            }
        }



        /// <summary>
        /// Get a spcefic MarketingProgramGroup
        /// </summary>
        /// <param name="marketingProgramGroupID"></param>
        /// <param name="marketingProgramGroupDescription"></param>
        /// <returns></returns>
        public static ERmgrUIP.SPGetMarketingProgramGroupRow GetMarketingProgramGroup(int marketingProgramGroupID, string marketingProgramGroupDescription)
        {
            ERmgrUIP.SPGetMarketingProgramGroupDataTable marketingProgramGroupDataTable = CommonDatabaseMethod.SPGetMarketingProgramGroupTableAdapter.GetData(marketingProgramGroupID, marketingProgramGroupDescription);

            if (marketingProgramGroupDataTable != null && marketingProgramGroupDataTable.Rows.Count > 0) return (ERmgrUIP.SPGetMarketingProgramGroupRow)marketingProgramGroupDataTable.Rows[0]; else return null;
        }

        static SPGetMarketingProgramGroupTableAdapter spGetMarketingProgramGroupTableAdapter;
        private static SPGetMarketingProgramGroupTableAdapter SPGetMarketingProgramGroupTableAdapter
        {
            get
            {
                if (spGetMarketingProgramGroupTableAdapter == null) spGetMarketingProgramGroupTableAdapter = new SPGetMarketingProgramGroupTableAdapter();
                return spGetMarketingProgramGroupTableAdapter;
            }
        }



        /// <summary>
        /// Get a spcefic MarketingProgramType
        /// </summary>
        /// <param name="marketingProgramTypeID"></param>
        /// <param name="marketingProgramTypeDescription"></param>
        /// <returns></returns>
        public static ERmgrUIP.SPGetMarketingProgramTypeRow GetMarketingProgramType(int marketingProgramTypeID, string marketingProgramTypeDescription)
        {
            ERmgrUIP.SPGetMarketingProgramTypeDataTable marketingProgramTypeDataTable = CommonDatabaseMethod.SPGetMarketingProgramTypeTableAdapter.GetData(marketingProgramTypeID, marketingProgramTypeDescription);

            if (marketingProgramTypeDataTable != null && marketingProgramTypeDataTable.Rows.Count > 0) return (ERmgrUIP.SPGetMarketingProgramTypeRow)marketingProgramTypeDataTable.Rows[0]; else return null;
        }

        static SPGetMarketingProgramTypeTableAdapter spGetMarketingProgramTypeTableAdapter;
        private static SPGetMarketingProgramTypeTableAdapter SPGetMarketingProgramTypeTableAdapter
        {
            get
            {
                if (spGetMarketingProgramTypeTableAdapter == null) spGetMarketingProgramTypeTableAdapter = new SPGetMarketingProgramTypeTableAdapter();
                return spGetMarketingProgramTypeTableAdapter;
            }
        }



        /// <summary>
        /// Get a spcefic TimeBase
        /// </summary>
        /// <param name="timeBaseID"></param>
        /// <param name="timeBaseDescription"></param>
        /// <returns></returns>
        public static ERmgrUIP.SPGetTimeBaseRow GetTimeBase(int timeBaseID, string timeBaseDescription)
        {
            ERmgrUIP.SPGetTimeBaseDataTable timeBaseDataTable = CommonDatabaseMethod.SPGetTimeBaseTableAdapter.GetData(timeBaseID, timeBaseDescription);

            if (timeBaseDataTable != null && timeBaseDataTable.Rows.Count > 0) return (ERmgrUIP.SPGetTimeBaseRow)timeBaseDataTable.Rows[0]; else return null;
        }

        static SPGetTimeBaseTableAdapter spGetTimeBaseTableAdapter;
        private static SPGetTimeBaseTableAdapter SPGetTimeBaseTableAdapter
        {
            get
            {
                if (spGetTimeBaseTableAdapter == null) spGetTimeBaseTableAdapter = new SPGetTimeBaseTableAdapter();
                return spGetTimeBaseTableAdapter;
            }
        }

        /// <summary>
        /// Get a spcefic MarketingPaymentType
        /// </summary>
        /// <param name="marketingPaymentTypeID"></param>
        /// <param name="marketingPaymentTypeDescription"></param>
        /// <returns></returns>
        public static ERmgrUIP.ListMarketingPaymentTypeRow GetMarketingPaymentType(int marketingPaymentTypeID, string marketingPaymentTypeDescription)
        {
            ERmgrUIP.ListMarketingPaymentTypeDataTable marketingPaymentTypeDataTable = CommonDatabaseMethod.MarketingPaymentTypeTableAdapter.GetDataBy(marketingPaymentTypeID, marketingPaymentTypeDescription);

            if (marketingPaymentTypeDataTable != null && marketingPaymentTypeDataTable.Rows.Count > 0) return (ERmgrUIP.ListMarketingPaymentTypeRow)marketingPaymentTypeDataTable.Rows[0]; else return null;
        }

        static ListMarketingPaymentTypeTableAdapter marketingPaymentTypeTableAdapter;
        private static ListMarketingPaymentTypeTableAdapter MarketingPaymentTypeTableAdapter
        {
            get
            {
                if (marketingPaymentTypeTableAdapter == null) marketingPaymentTypeTableAdapter = new ListMarketingPaymentTypeTableAdapter();
                return marketingPaymentTypeTableAdapter;
            }
        }


        /// <summary>
        /// Get a spcefic MarketingPaymentTerm
        /// </summary>
        /// <param name="marketingPaymentTermID"></param>
        /// <param name="marketingPaymentTermDescription"></param>
        /// <returns></returns>
        public static ERmgrUIP.SPGetMarketingPaymentTermRow GetMarketingPaymentTerm(int marketingPaymentTermID, string marketingPaymentTermDescription)
        {
            ERmgrUIP.SPGetMarketingPaymentTermDataTable marketingPaymentTermDataTable = CommonDatabaseMethod.SPGetMarketingPaymentTermTableAdapter.GetData(marketingPaymentTermID, marketingPaymentTermDescription);

            if (marketingPaymentTermDataTable != null && marketingPaymentTermDataTable.Rows.Count > 0) return (ERmgrUIP.SPGetMarketingPaymentTermRow)marketingPaymentTermDataTable.Rows[0]; else return null;
        }

        static SPGetMarketingPaymentTermTableAdapter spGetMarketingPaymentTermTableAdapter;
        private static SPGetMarketingPaymentTermTableAdapter SPGetMarketingPaymentTermTableAdapter
        {
            get
            {
                if (spGetMarketingPaymentTermTableAdapter == null) spGetMarketingPaymentTermTableAdapter = new SPGetMarketingPaymentTermTableAdapter();
                return spGetMarketingPaymentTermTableAdapter;
            }
        }



        ///// <summary>
        ///// Get a spcefic SalesmenName
        ///// </summary>
        ///// <param name="salesmenID"></param>
        ///// <param name="salesmenNameDescription"></param>
        ///// <returns></returns>
        //public static ERmgrUIP.SPGetSalesmenNameRow GetSalesmenName(int salesmenID, string salesmenNameDescription)
        //{
        //    ERmgrUIP.SPGetSalesmenNameDataTable salesmenNameDataTable = CommonDatabaseMethod.SPGetSalesmenNameTableAdapter.GetData(salesmenID, salesmenNameDescription);

        //    if (salesmenNameDataTable != null && salesmenNameDataTable.Rows.Count > 0) return (ERmgrUIP.SPGetSalesmenNameRow)salesmenNameDataTable.Rows[0]; else return null;
        //}

        //static SPGetSalesmenNameTableAdapter spGetSalesmenNameTableAdapter;
        //private static SPGetSalesmenNameTableAdapter SPGetSalesmenNameTableAdapter
        //{
        //    get
        //    {
        //        if (spGetSalesmenNameTableAdapter == null) spGetSalesmenNameTableAdapter = new SPGetSalesmenNameTableAdapter();
        //        return spGetSalesmenNameTableAdapter;
        //    }
        //}


        /// <summary>
        /// Get a spcefic StaffName
        /// </summary>
        /// <param name="staffNameID"></param>
        /// <param name="staffNameDescription"></param>
        /// <returns></returns>
        public static ERmgrUIP.ListStaffNameRow GetStaffName(int staffNameID, string staffNameDescription)
        {
            ERmgrUIP.ListStaffNameDataTable staffNameDataTable = CommonDatabaseMethod.StaffNameTableAdapter.GetDataBy(staffNameID, staffNameDescription);

            if (staffNameDataTable != null && staffNameDataTable.Rows.Count > 0) return (ERmgrUIP.ListStaffNameRow)staffNameDataTable.Rows[0]; else return null;
        }

        static ListStaffNameTableAdapter staffNameTableAdapter;
        private static ListStaffNameTableAdapter StaffNameTableAdapter
        {
            get
            {
                if (staffNameTableAdapter == null) staffNameTableAdapter = new ListStaffNameTableAdapter();
                return staffNameTableAdapter;
            }
        }



        /// <summary>
        /// Get a spcefic EmployeeMaster
        /// </summary>
        /// <param name="EmployeeID"></param>
        /// <param name="EmployeeName"></param>
        /// <returns></returns>
        public static ERmgrUIP.SPGetEmployeeMasterRow GetEmployeeMaster(int EmployeeID, string EmployeeName)
        {
            ERmgrUIP.SPGetEmployeeMasterDataTable EmployeeMasterDataTable = CommonDatabaseMethod.SPGetEmployeeMasterTableAdapter.GetData(EmployeeID, EmployeeName);

            if (EmployeeMasterDataTable != null && EmployeeMasterDataTable.Rows.Count > 0) return (ERmgrUIP.SPGetEmployeeMasterRow)EmployeeMasterDataTable.Rows[0]; else return null;
        }

        static SPGetEmployeeMasterTableAdapter spGetEmployeeMasterTableAdapter;
        private static SPGetEmployeeMasterTableAdapter SPGetEmployeeMasterTableAdapter
        {
            get
            {
                if (spGetEmployeeMasterTableAdapter == null) spGetEmployeeMasterTableAdapter = new SPGetEmployeeMasterTableAdapter();
                return spGetEmployeeMasterTableAdapter;
            }
        }




        /// <summary>
        /// Get a spcefic MarketingProgram
        /// </summary>
        /// <param name="marketingProgramID"></param>
        /// <param name="reference"></param>
        /// <returns></returns>
        public static ERmgrUIP.SPGetMarketingProgramRow GetMarketingProgram(int marketingProgramID, string reference)
        {
            ERmgrUIP.SPGetMarketingProgramDataTable marketingProgramDataTable = CommonDatabaseMethod.SPGetMarketingProgramTableAdapter.GetData(marketingProgramID, reference);

            if (marketingProgramDataTable != null && marketingProgramDataTable.Rows.Count > 0) return (ERmgrUIP.SPGetMarketingProgramRow)marketingProgramDataTable.Rows[0]; else return null;
        }

        static SPGetMarketingProgramTableAdapter spGetMarketingProgramTableAdapter;
        private static SPGetMarketingProgramTableAdapter SPGetMarketingProgramTableAdapter
        {
            get
            {
                if (spGetMarketingProgramTableAdapter == null) spGetMarketingProgramTableAdapter = new SPGetMarketingProgramTableAdapter();
                return spGetMarketingProgramTableAdapter;
            }
        }



        /// <summary>
        /// Get a spcefic ListSalesmenName
        /// </summary>
        /// <param name="salesmenID"></param>
        /// <param name="description"></param>
        /// <returns></returns>
        public static ERmgrUIP.SPGetSalesmenNameRow GetSalesmenName(int salesmenID, string description)
        {
            ERmgrUIP.SPGetSalesmenNameDataTable salesmenNameDataTable = CommonDatabaseMethod.SPGetSalesmenNameTableAdapter.GetData(salesmenID, description);

            if (salesmenNameDataTable != null && salesmenNameDataTable.Rows.Count > 0) return (ERmgrUIP.SPGetSalesmenNameRow)salesmenNameDataTable.Rows[0]; else return null;
        }

        static SPGetSalesmenNameTableAdapter spGetSalesmenNameTableAdapter;
        private static SPGetSalesmenNameTableAdapter SPGetSalesmenNameTableAdapter
        {
            get
            {
                if (spGetSalesmenNameTableAdapter == null) spGetSalesmenNameTableAdapter = new SPGetSalesmenNameTableAdapter();
                return spGetSalesmenNameTableAdapter;
            }
        }


        #region Restore Procedure

        private static void RestoreProcedure()
        {
            CommonDatabaseMethod.RestoreSPGetItemCategory();
            CommonDatabaseMethod.RestoreSPGetItemType();
            CommonDatabaseMethod.RestoreSPGetItemCommodity();

            CommonDatabaseMethod.RestoreSPGetAddressArea();

            CommonDatabaseMethod.RestoreSPGetCustomerChannel();
            CommonDatabaseMethod.RestoreSPGetCustomerName();

            CommonDatabaseMethod.RestoreSPGetMarketingProgramGroup();
            CommonDatabaseMethod.RestoreSPGetMarketingProgramType();
            CommonDatabaseMethod.RestoreSPGetTimeBase();

            CommonDatabaseMethod.RestoreSPGetMarketingPaymentType();
            CommonDatabaseMethod.RestoreSPGetMarketingPaymentTerm();

            CommonDatabaseMethod.RestoreSPGetStaffName();
            CommonDatabaseMethod.RestoreSPGetEmployeeMaster();

            CommonDatabaseMethod.RestoreSPGetMarketingProgram();
            CommonDatabaseMethod.RestoreSPGetSalesmenName();
            CommonDatabaseMethod.RestoreSPGetCustomerCategory();
            CommonDatabaseMethod.RestoreSPGetCustomerType();
        }








        private static void RestoreSPGetItemCategory()
        {
            string queryString = ""; string queryFrom = ""; string queryWhere = "";

            queryFrom = "       DECLARE   Local_Cursor      CURSOR LOCAL FOR     SELECT TOP 1 ItemCategoryID FROM ListItemCategory WHERE " + "\r\n";
            queryWhere = "      "; //AND InActive = 0 AND ((@IsCustomer = 1 AND ListItemCategory.IsCustomer = 1) OR (@IsSupplier = 1 AND ListItemCategory.IsSupplier = 1)) " + "\r\n";

            queryString = "     @ItemCategoryID Int, @ItemCategoryDescription nvarChar (200) " + "\r\n"; //, @IsCustomer Int, @IsSupplier Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";

            //Step 1: FIND ItemCategoryID.BEGIN
            queryString = queryString + "   IF @ItemCategoryID <= 0 AND @ItemCategoryDescription <> '' " + "\r\n"; // FIND @ItemCategoryID BY @ItemCategory
            queryString = queryString + "   BEGIN " + "\r\n";

            queryString = queryString + "       DECLARE @lLoop Int " + "\r\n"; //BIEN LOOP DE TIM ItemCategoryID
            queryString = queryString + "       SET @lLoop = -1 " + "\r\n";

            queryString = queryString + "       WHILE @lLoop <= 3 " + "\r\n";
            queryString = queryString + "           BEGIN " + "\r\n";
            queryString = queryString + "               IF @lLoop = -1 " + "\r\n";
            queryString = queryString + "                   " + queryFrom + " Description = @ItemCategoryDescription " + queryWhere + "\r\n";
            queryString = queryString + "               ELSE " + "\r\n";
            queryString = queryString + "                   IF @lLoop = 0 " + "\r\n";
            queryString = queryString + "                       " + queryFrom + " Description LIKE @ItemCategoryDescription + '%' " + queryWhere + "\r\n";
            queryString = queryString + "                   ELSE " + "\r\n"; //@lLoop = 3
            queryString = queryString + "                       " + queryFrom + " Description LIKE '%' + @ItemCategoryDescription + '%' " + queryWhere + "\r\n";

            queryString = queryString + "               OPEN Local_Cursor " + "\r\n";

            queryString = queryString + "               FETCH NEXT FROM Local_Cursor INTO @ItemCategoryID " + "\r\n";

            queryString = queryString + "               CLOSE Local_Cursor " + "\r\n";
            queryString = queryString + "               DEALLOCATE Local_Cursor " + "\r\n";

            queryString = queryString + "               SET @lLoop = @lLoop + 1 " + "\r\n";

            queryString = queryString + "               IF @ItemCategoryID > 0 " + "\r\n";
            queryString = queryString + "                   BREAK " + "\r\n";//ItemCategoryID FOUND
            queryString = queryString + "               Else " + "\r\n";
            queryString = queryString + "                   CONTINUE " + "\r\n";

            queryString = queryString + "           END " + "\r\n"; //END LOOP

            queryString = queryString + "   END " + "\r\n";
            //Step 1: FIND ItemCategoryID.END

            //Step 2: @ItemCategoryID FOUND OR @ItemCategoryID <> 0
            queryString = queryString + "   IF @ItemCategoryID > 0 " + "\r\n";
            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           SELECT      TOP 1 ItemCategoryID, Description " + "\r\n";
            queryString = queryString + "           FROM        ListItemCategory " + "\r\n";
            queryString = queryString + "           WHERE       ItemCategoryID = @ItemCategoryID " + "\r\n";
            queryString = queryString + "       END " + "\r\n";

            SQLDatabase.CreateStoredProcedure("SPGetItemCategory", queryString);
        }


        private static void RestoreSPGetItemType()
        {
            string queryString = ""; string queryFrom = ""; string queryWhere = "";

            queryFrom = "       DECLARE   Local_Cursor      CURSOR LOCAL FOR     SELECT TOP 1 ItemTypeID FROM ListItemType WHERE " + "\r\n";
            queryWhere = "      "; //AND InActive = 0 AND ((@IsCustomer = 1 AND ListItemType.IsCustomer = 1) OR (@IsSupplier = 1 AND ListItemType.IsSupplier = 1)) " + "\r\n";

            queryString = "     @ItemTypeID Int, @ItemTypeDescription nvarChar (200) " + "\r\n"; //, @IsCustomer Int, @IsSupplier Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";

            //Step 1: FIND ItemTypeID.BEGIN
            queryString = queryString + "   IF @ItemTypeID <= 0 AND @ItemTypeDescription <> '' " + "\r\n"; // FIND @ItemTypeID BY @ItemType
            queryString = queryString + "   BEGIN " + "\r\n";

            queryString = queryString + "       DECLARE @lLoop Int " + "\r\n"; //BIEN LOOP DE TIM ItemTypeID
            queryString = queryString + "       SET @lLoop = -1 " + "\r\n";

            queryString = queryString + "       WHILE @lLoop <= 3 " + "\r\n";
            queryString = queryString + "           BEGIN " + "\r\n";
            queryString = queryString + "               IF @lLoop = -1 " + "\r\n";
            queryString = queryString + "                   " + queryFrom + " Description = @ItemTypeDescription " + queryWhere + "\r\n";
            queryString = queryString + "               ELSE " + "\r\n";
            queryString = queryString + "                   IF @lLoop = 0 " + "\r\n";
            queryString = queryString + "                       " + queryFrom + " Description LIKE @ItemTypeDescription + '%' " + queryWhere + "\r\n";
            queryString = queryString + "                   ELSE " + "\r\n"; //@lLoop = 3
            queryString = queryString + "                       " + queryFrom + " Description LIKE '%' + @ItemTypeDescription + '%' " + queryWhere + "\r\n";

            queryString = queryString + "               OPEN Local_Cursor " + "\r\n";

            queryString = queryString + "               FETCH NEXT FROM Local_Cursor INTO @ItemTypeID " + "\r\n";

            queryString = queryString + "               CLOSE Local_Cursor " + "\r\n";
            queryString = queryString + "               DEALLOCATE Local_Cursor " + "\r\n";

            queryString = queryString + "               SET @lLoop = @lLoop + 1 " + "\r\n";

            queryString = queryString + "               IF @ItemTypeID > 0 " + "\r\n";
            queryString = queryString + "                   BREAK " + "\r\n";//ItemTypeID FOUND
            queryString = queryString + "               Else " + "\r\n";
            queryString = queryString + "                   CONTINUE " + "\r\n";

            queryString = queryString + "           END " + "\r\n"; //END LOOP

            queryString = queryString + "   END " + "\r\n";
            //Step 1: FIND ItemTypeID.END

            //Step 2: @ItemTypeID FOUND OR @ItemTypeID <> 0
            queryString = queryString + "   IF @ItemTypeID > 0 " + "\r\n";
            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           SELECT      TOP 1 ItemTypeID, Description " + "\r\n";
            queryString = queryString + "           FROM        ListItemType " + "\r\n";
            queryString = queryString + "           WHERE       ItemTypeID = @ItemTypeID " + "\r\n";
            queryString = queryString + "       END " + "\r\n";

            SQLDatabase.CreateStoredProcedure("SPGetItemType", queryString);
        }


        private static void RestoreSPGetItemCommodity()
        {
            string queryString = ""; string queryFrom = ""; string queryWhere = "";

            queryFrom = "       DECLARE   Local_Cursor      CURSOR LOCAL FOR     SELECT TOP 1 CommodityID FROM ListItemCommodity WHERE " + "\r\n";
            queryWhere = "      "; //AND ((@IsCommodity = 1 AND IsCommodity = 1) OR (@IsSupplier = 1 AND IsSupplier = 1)) " + "\r\n";

            queryString = "     @CommodityID Int, @CommodityDescription nvarChar (200) " + "\r\n"; //, @IsCommodity Int, @IsSupplier Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";

            //Step 1: FIND CommodityID.BEGIN
            queryString = queryString + "   IF @CommodityID <= 0 AND @CommodityDescription  <> '' " + "\r\n"; // FIND @CommodityID BY @CommodityDescription 
            queryString = queryString + "   BEGIN " + "\r\n";

            queryString = queryString + "       DECLARE @lLoop Int " + "\r\n"; //BIEN LOOP DE TIM CommodityID
            queryString = queryString + "       SET @lLoop = -1 " + "\r\n";

            queryString = queryString + "       WHILE @lLoop <= 5 " + "\r\n";
            queryString = queryString + "           BEGIN " + "\r\n";
            queryString = queryString + "               IF @lLoop = -1 " + "\r\n";
            queryString = queryString + "                   " + queryFrom + " Description + '-' + DescriptionOfficial = @CommodityDescription  AND InActive = 0 " + queryWhere + "\r\n";
            queryString = queryString + "               ELSE " + "\r\n";
            queryString = queryString + "                   IF @lLoop = 0 " + "\r\n";
            queryString = queryString + "                       " + queryFrom + " DescriptionOfficial + '-' + Description = @CommodityDescription  AND InActive = 0 " + queryWhere + "\r\n";
            queryString = queryString + "                   ELSE " + "\r\n";
            queryString = queryString + "                       IF @lLoop = 1 " + "\r\n";
            queryString = queryString + "                           " + queryFrom + " DescriptionOfficial = @CommodityDescription  AND InActive = 0 " + queryWhere + "\r\n";
            queryString = queryString + "                       ELSE " + "\r\n";
            queryString = queryString + "                           IF @lLoop = 2 " + "\r\n";
            queryString = queryString + "                               " + queryFrom + " Description = @CommodityDescription  AND InActive = 0 " + queryWhere + "\r\n";
            queryString = queryString + "                           ELSE " + "\r\n";
            queryString = queryString + "                               IF @lLoop = 3 " + "\r\n";
            queryString = queryString + "                                   " + queryFrom + " DescriptionOfficial LIKE '%' + @CommodityDescription  + '%' AND InActive = 0 " + queryWhere + "\r\n";
            queryString = queryString + "                               ELSE " + "\r\n";
            queryString = queryString + "                                   IF @lLoop = 4 " + "\r\n";
            queryString = queryString + "                                       " + queryFrom + " Description LIKE @CommodityDescription  + '%' AND InActive = 0 " + queryWhere + "\r\n";
            queryString = queryString + "                                   ELSE " + "\r\n"; //@lLoop = 5
            queryString = queryString + "                                       " + queryFrom + " Description LIKE '%' + @CommodityDescription  + '%' AND InActive = 0 " + queryWhere + "\r\n";

            queryString = queryString + "               OPEN Local_Cursor " + "\r\n";

            queryString = queryString + "               FETCH NEXT FROM Local_Cursor INTO @CommodityID " + "\r\n";

            queryString = queryString + "               CLOSE Local_Cursor " + "\r\n";
            queryString = queryString + "               DEALLOCATE Local_Cursor " + "\r\n";

            queryString = queryString + "               SET @lLoop = @lLoop + 1 " + "\r\n";

            queryString = queryString + "               IF @CommodityID > 0 " + "\r\n";
            queryString = queryString + "                   BREAK " + "\r\n";//CommodityID FOUND
            queryString = queryString + "               Else " + "\r\n";
            queryString = queryString + "                   CONTINUE " + "\r\n";

            queryString = queryString + "           END " + "\r\n"; //END LOOP

            queryString = queryString + "   END " + "\r\n";
            //Step 1: FIND CommodityID.END

            //Step 2: @CommodityID FOUND OR @CommodityID <> 0
            queryString = queryString + "   IF @CommodityID > 0 " + "\r\n";
            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           SELECT      TOP 1 CommodityID, Description, DescriptionOfficial " + "\r\n";
            queryString = queryString + "           FROM        ListItemCommodity " + "\r\n";
            queryString = queryString + "           WHERE       CommodityID = @CommodityID " + "\r\n";
            queryString = queryString + "       END " + "\r\n";

            SQLDatabase.CreateStoredProcedure("SPGetItemCommodity", queryString);
        }





        private static void RestoreSPGetAddressArea()
        {
            string queryString = ""; string queryFrom = ""; string queryWhere = "";

            queryFrom = "       DECLARE   Local_Cursor      CURSOR LOCAL FOR     SELECT TOP 1 AddressAreaID FROM ListAddressArea WHERE " + "\r\n";
            queryWhere = "      "; //AND InActive = 0 AND ((@IsCustomer = 1 AND ListAddressArea.IsCustomer = 1) OR (@IsSupplier = 1 AND ListAddressArea.IsSupplier = 1)) " + "\r\n";

            queryString = "     @AddressAreaID Int, @AddressAreaDescription nvarChar (200) " + "\r\n"; //, @IsCustomer Int, @IsSupplier Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";

            //Step 1: FIND AddressAreaID.BEGIN
            queryString = queryString + "   IF @AddressAreaID <= 0 AND @AddressAreaDescription <> '' " + "\r\n"; // FIND @AddressAreaID BY @AddressArea
            queryString = queryString + "   BEGIN " + "\r\n";

            queryString = queryString + "       DECLARE @lLoop Int " + "\r\n"; //BIEN LOOP DE TIM AddressAreaID
            queryString = queryString + "       SET @lLoop = -1 " + "\r\n";

            queryString = queryString + "       WHILE @lLoop <= 3 " + "\r\n";
            queryString = queryString + "           BEGIN " + "\r\n";
            queryString = queryString + "               IF @lLoop = -1 " + "\r\n";
            queryString = queryString + "                   " + queryFrom + " Description = @AddressAreaDescription " + queryWhere + "\r\n";
            queryString = queryString + "               ELSE " + "\r\n";
            queryString = queryString + "                   IF @lLoop = 0 " + "\r\n";
            queryString = queryString + "                       " + queryFrom + " Description LIKE @AddressAreaDescription + '%' " + queryWhere + "\r\n";
            queryString = queryString + "                   ELSE " + "\r\n"; //@lLoop = 3
            queryString = queryString + "                       " + queryFrom + " Description LIKE '%' + @AddressAreaDescription + '%' " + queryWhere + "\r\n";

            queryString = queryString + "               OPEN Local_Cursor " + "\r\n";

            queryString = queryString + "               FETCH NEXT FROM Local_Cursor INTO @AddressAreaID " + "\r\n";

            queryString = queryString + "               CLOSE Local_Cursor " + "\r\n";
            queryString = queryString + "               DEALLOCATE Local_Cursor " + "\r\n";

            queryString = queryString + "               SET @lLoop = @lLoop + 1 " + "\r\n";

            queryString = queryString + "               IF @AddressAreaID > 0 " + "\r\n";
            queryString = queryString + "                   BREAK " + "\r\n";//AddressAreaID FOUND
            queryString = queryString + "               Else " + "\r\n";
            queryString = queryString + "                   CONTINUE " + "\r\n";

            queryString = queryString + "           END " + "\r\n"; //END LOOP

            queryString = queryString + "   END " + "\r\n";
            //Step 1: FIND AddressAreaID.END

            //Step 2: @AddressAreaID FOUND OR @AddressAreaID <> 0
            queryString = queryString + "   IF @AddressAreaID > 0 " + "\r\n";
            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           SELECT      TOP 1 AddressAreaID, Description " + "\r\n";
            queryString = queryString + "           FROM        ListAddressArea " + "\r\n";
            queryString = queryString + "           WHERE       AddressAreaID = @AddressAreaID " + "\r\n";
            queryString = queryString + "       END " + "\r\n";

            SQLDatabase.CreateStoredProcedure("SPGetAddressArea", queryString);
        }




        private static void RestoreSPGetCustomerChannel()
        {
            string queryString = ""; string queryFrom = ""; string queryWhere = "";

            queryFrom = "       DECLARE   Local_Cursor      CURSOR LOCAL FOR     SELECT TOP 1 CustomerChannelID FROM ListCustomerChannel WHERE " + "\r\n";
            queryWhere = "      AND (@PrimaryChannelOnly = 0 OR AncestorID = 0) "; //AND InActive = 0 AND ((@IsCustomer = 1 AND ListCustomerChannel.IsCustomer = 1) OR (@IsSupplier = 1 AND ListCustomerChannel.IsSupplier = 1)) " + "\r\n";

            queryString = "     @CustomerChannelID Int, @CustomerChannelDescription nvarChar (200), @PrimaryChannelOnly bit " + "\r\n"; //, @IsCustomer Int, @IsSupplier Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";

            //Step 1: FIND CustomerChannelID.BEGIN
            queryString = queryString + "   IF @CustomerChannelID <= 0 AND @CustomerChannelDescription <> '' " + "\r\n"; // FIND @CustomerChannelID BY @CustomerChannel
            queryString = queryString + "   BEGIN " + "\r\n";

            queryString = queryString + "       DECLARE @lLoop Int " + "\r\n"; //BIEN LOOP DE TIM CustomerChannelID
            queryString = queryString + "       SET @lLoop = -1 " + "\r\n";

            queryString = queryString + "       WHILE @lLoop <= 3 " + "\r\n";
            queryString = queryString + "           BEGIN " + "\r\n";
            queryString = queryString + "               IF @lLoop = -1 " + "\r\n";
            queryString = queryString + "                   " + queryFrom + " Description = @CustomerChannelDescription " + queryWhere + "\r\n";
            queryString = queryString + "               ELSE " + "\r\n";
            queryString = queryString + "                   IF @lLoop = 0 " + "\r\n";
            queryString = queryString + "                       " + queryFrom + " Description LIKE @CustomerChannelDescription + '%' " + queryWhere + "\r\n";
            queryString = queryString + "                   ELSE " + "\r\n"; //@lLoop = 3
            queryString = queryString + "                       " + queryFrom + " Description LIKE '%' + @CustomerChannelDescription + '%' " + queryWhere + "\r\n";

            queryString = queryString + "               OPEN Local_Cursor " + "\r\n";

            queryString = queryString + "               FETCH NEXT FROM Local_Cursor INTO @CustomerChannelID " + "\r\n";

            queryString = queryString + "               CLOSE Local_Cursor " + "\r\n";
            queryString = queryString + "               DEALLOCATE Local_Cursor " + "\r\n";

            queryString = queryString + "               SET @lLoop = @lLoop + 1 " + "\r\n";

            queryString = queryString + "               IF @CustomerChannelID > 0 " + "\r\n";
            queryString = queryString + "                   BREAK " + "\r\n";//CustomerChannelID FOUND
            queryString = queryString + "               Else " + "\r\n";
            queryString = queryString + "                   CONTINUE " + "\r\n";

            queryString = queryString + "           END " + "\r\n"; //END LOOP

            queryString = queryString + "   END " + "\r\n";
            //Step 1: FIND CustomerChannelID.END

            //Step 2: @CustomerChannelID FOUND OR @CustomerChannelID <> 0
            queryString = queryString + "   IF @CustomerChannelID > 0 " + "\r\n";
            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           SELECT      TOP 1 CustomerChannelID, Description " + "\r\n";
            queryString = queryString + "           FROM        ListCustomerChannel " + "\r\n";
            queryString = queryString + "           WHERE       CustomerChannelID = @CustomerChannelID " + "\r\n";
            queryString = queryString + "       END " + "\r\n";

            SQLDatabase.CreateStoredProcedure("SPGetCustomerChannel", queryString);
        }



        private static void RestoreSPGetCustomerCategory()
        {
            string queryString = ""; string queryFrom = ""; string queryWhere = "";

            queryFrom = "       DECLARE   Local_Cursor      CURSOR LOCAL FOR     SELECT TOP 1 CustomerCategoryID FROM ListCustomerCategory WHERE " + "\r\n";
            queryWhere = "      AND (@PrimaryChannelOnly = 0 OR AncestorID = 0) "; //AND InActive = 0 AND ((@IsCustomer = 1 AND ListCustomerCategory.IsCustomer = 1) OR (@IsSupplier = 1 AND ListCustomerCategory.IsSupplier = 1)) " + "\r\n";

            queryString = "     @CustomerCategoryID Int, @CustomerCategoryDescription nvarChar (200), @PrimaryChannelOnly bit " + "\r\n"; //, @IsCustomer Int, @IsSupplier Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";

            //Step 1: FIND CustomerCategoryID.BEGIN
            queryString = queryString + "   IF @CustomerCategoryID <= 0 AND @CustomerCategoryDescription <> '' " + "\r\n"; // FIND @CustomerCategoryID BY @CustomerCategory
            queryString = queryString + "   BEGIN " + "\r\n";

            queryString = queryString + "       DECLARE @lLoop Int " + "\r\n"; //BIEN LOOP DE TIM CustomerCategoryID
            queryString = queryString + "       SET @lLoop = -1 " + "\r\n";

            queryString = queryString + "       WHILE @lLoop <= 3 " + "\r\n";
            queryString = queryString + "           BEGIN " + "\r\n";
            queryString = queryString + "               IF @lLoop = -1 " + "\r\n";
            queryString = queryString + "                   " + queryFrom + " Description = @CustomerCategoryDescription " + queryWhere + "\r\n";
            queryString = queryString + "               ELSE " + "\r\n";
            queryString = queryString + "                   IF @lLoop = 0 " + "\r\n";
            queryString = queryString + "                       " + queryFrom + " Description LIKE @CustomerCategoryDescription + '%' " + queryWhere + "\r\n";
            queryString = queryString + "                   ELSE " + "\r\n"; //@lLoop = 3
            queryString = queryString + "                       " + queryFrom + " Description LIKE '%' + @CustomerCategoryDescription + '%' " + queryWhere + "\r\n";

            queryString = queryString + "               OPEN Local_Cursor " + "\r\n";

            queryString = queryString + "               FETCH NEXT FROM Local_Cursor INTO @CustomerCategoryID " + "\r\n";

            queryString = queryString + "               CLOSE Local_Cursor " + "\r\n";
            queryString = queryString + "               DEALLOCATE Local_Cursor " + "\r\n";

            queryString = queryString + "               SET @lLoop = @lLoop + 1 " + "\r\n";

            queryString = queryString + "               IF @CustomerCategoryID > 0 " + "\r\n";
            queryString = queryString + "                   BREAK " + "\r\n";//CustomerCategoryID FOUND
            queryString = queryString + "               Else " + "\r\n";
            queryString = queryString + "                   CONTINUE " + "\r\n";

            queryString = queryString + "           END " + "\r\n"; //END LOOP

            queryString = queryString + "   END " + "\r\n";
            //Step 1: FIND CustomerCategoryID.END

            //Step 2: @CustomerCategoryID FOUND OR @CustomerCategoryID <> 0
            queryString = queryString + "   IF @CustomerCategoryID > 0 " + "\r\n";
            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           SELECT      TOP 1 CustomerCategoryID, Description " + "\r\n";
            queryString = queryString + "           FROM        ListCustomerCategory " + "\r\n";
            queryString = queryString + "           WHERE       CustomerCategoryID = @CustomerCategoryID " + "\r\n";
            queryString = queryString + "       END " + "\r\n";

            SQLDatabase.CreateStoredProcedure("SPGetCustomerCategory", queryString);
        }



        private static void RestoreSPGetCustomerType()
        {
            string queryString = ""; string queryFrom = ""; string queryWhere = "";

            queryFrom = "       DECLARE   Local_Cursor      CURSOR LOCAL FOR     SELECT TOP 1 CustomerTypeID FROM ListCustomerType WHERE " + "\r\n";
            queryWhere = "      AND (@PrimaryChannelOnly = 0 OR AncestorID = 0) "; //AND InActive = 0 AND ((@IsCustomer = 1 AND ListCustomerType.IsCustomer = 1) OR (@IsSupplier = 1 AND ListCustomerType.IsSupplier = 1)) " + "\r\n";

            queryString = "     @CustomerTypeID Int, @CustomerTypeDescription nvarChar (200), @PrimaryChannelOnly bit " + "\r\n"; //, @IsCustomer Int, @IsSupplier Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";

            //Step 1: FIND CustomerTypeID.BEGIN
            queryString = queryString + "   IF @CustomerTypeID <= 0 AND @CustomerTypeDescription <> '' " + "\r\n"; // FIND @CustomerTypeID BY @CustomerType
            queryString = queryString + "   BEGIN " + "\r\n";

            queryString = queryString + "       DECLARE @lLoop Int " + "\r\n"; //BIEN LOOP DE TIM CustomerTypeID
            queryString = queryString + "       SET @lLoop = -1 " + "\r\n";

            queryString = queryString + "       WHILE @lLoop <= 3 " + "\r\n";
            queryString = queryString + "           BEGIN " + "\r\n";
            queryString = queryString + "               IF @lLoop = -1 " + "\r\n";
            queryString = queryString + "                   " + queryFrom + " Description = @CustomerTypeDescription " + queryWhere + "\r\n";
            queryString = queryString + "               ELSE " + "\r\n";
            queryString = queryString + "                   IF @lLoop = 0 " + "\r\n";
            queryString = queryString + "                       " + queryFrom + " Description LIKE @CustomerTypeDescription + '%' " + queryWhere + "\r\n";
            queryString = queryString + "                   ELSE " + "\r\n"; //@lLoop = 3
            queryString = queryString + "                       " + queryFrom + " Description LIKE '%' + @CustomerTypeDescription + '%' " + queryWhere + "\r\n";

            queryString = queryString + "               OPEN Local_Cursor " + "\r\n";

            queryString = queryString + "               FETCH NEXT FROM Local_Cursor INTO @CustomerTypeID " + "\r\n";

            queryString = queryString + "               CLOSE Local_Cursor " + "\r\n";
            queryString = queryString + "               DEALLOCATE Local_Cursor " + "\r\n";

            queryString = queryString + "               SET @lLoop = @lLoop + 1 " + "\r\n";

            queryString = queryString + "               IF @CustomerTypeID > 0 " + "\r\n";
            queryString = queryString + "                   BREAK " + "\r\n";//CustomerTypeID FOUND
            queryString = queryString + "               Else " + "\r\n";
            queryString = queryString + "                   CONTINUE " + "\r\n";

            queryString = queryString + "           END " + "\r\n"; //END LOOP

            queryString = queryString + "   END " + "\r\n";
            //Step 1: FIND CustomerTypeID.END

            //Step 2: @CustomerTypeID FOUND OR @CustomerTypeID <> 0
            queryString = queryString + "   IF @CustomerTypeID > 0 " + "\r\n";
            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           SELECT      TOP 1 CustomerTypeID, Description " + "\r\n";
            queryString = queryString + "           FROM        ListCustomerType " + "\r\n";
            queryString = queryString + "           WHERE       CustomerTypeID = @CustomerTypeID " + "\r\n";
            queryString = queryString + "       END " + "\r\n";

            SQLDatabase.CreateStoredProcedure("SPGetCustomerType", queryString);
        }


        private static void RestoreSPGetCustomerName()
        {
            string queryString = ""; string queryFrom = ""; string queryWhere = "";

            queryFrom = "       DECLARE   Local_Cursor      CURSOR LOCAL FOR     SELECT TOP 1 CustomerID FROM ListCustomerName WHERE " + "\r\n";
            queryWhere = "      "; //AND ((@IsCustomer = 1 AND IsCustomer = 1) OR (@IsSupplier = 1 AND IsSupplier = 1)) " + "\r\n";

            queryString = "     @CustomerID Int, @CustomerDescription nvarChar (200) " + "\r\n"; //, @IsCustomer Int, @IsSupplier Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";

            //Step 1: FIND CustomerID.BEGIN
            queryString = queryString + "   IF @CustomerID <= 0 AND @CustomerDescription  <> '' " + "\r\n"; // FIND @CustomerID BY @CustomerDescription 
            queryString = queryString + "   BEGIN " + "\r\n";

            queryString = queryString + "       DECLARE @lLoop Int " + "\r\n"; //BIEN LOOP DE TIM CustomerID
            queryString = queryString + "       SET @lLoop = -1 " + "\r\n";

            queryString = queryString + "       WHILE @lLoop <= 5 " + "\r\n";
            queryString = queryString + "           BEGIN " + "\r\n";
            queryString = queryString + "               IF @lLoop = -1 " + "\r\n";
            queryString = queryString + "                   " + queryFrom + " Description + '-' + DescriptionOfficial = @CustomerDescription  AND InActive = 0 " + queryWhere + "\r\n";
            queryString = queryString + "               ELSE " + "\r\n";
            queryString = queryString + "                   IF @lLoop = 0 " + "\r\n";
            queryString = queryString + "                       " + queryFrom + " DescriptionOfficial + '-' + Description = @CustomerDescription  AND InActive = 0 " + queryWhere + "\r\n";
            queryString = queryString + "                   ELSE " + "\r\n";
            queryString = queryString + "                       IF @lLoop = 1 " + "\r\n";
            queryString = queryString + "                           " + queryFrom + " DescriptionOfficial = @CustomerDescription  AND InActive = 0 " + queryWhere + "\r\n";
            queryString = queryString + "                       ELSE " + "\r\n";
            queryString = queryString + "                           IF @lLoop = 2 " + "\r\n";
            queryString = queryString + "                               " + queryFrom + " Description = @CustomerDescription  AND InActive = 0 " + queryWhere + "\r\n";
            queryString = queryString + "                           ELSE " + "\r\n";
            queryString = queryString + "                               IF @lLoop = 3 " + "\r\n";
            queryString = queryString + "                                   " + queryFrom + " DescriptionOfficial LIKE '%' + @CustomerDescription  + '%' AND InActive = 0 " + queryWhere + "\r\n";
            queryString = queryString + "                               ELSE " + "\r\n";
            queryString = queryString + "                                   IF @lLoop = 4 " + "\r\n";
            queryString = queryString + "                                       " + queryFrom + " Description LIKE @CustomerDescription  + '%' AND InActive = 0 " + queryWhere + "\r\n";
            queryString = queryString + "                                   ELSE " + "\r\n"; //@lLoop = 5
            queryString = queryString + "                                       " + queryFrom + " Description LIKE '%' + @CustomerDescription  + '%' AND InActive = 0 " + queryWhere + "\r\n";

            queryString = queryString + "               OPEN Local_Cursor " + "\r\n";

            queryString = queryString + "               FETCH NEXT FROM Local_Cursor INTO @CustomerID " + "\r\n";

            queryString = queryString + "               CLOSE Local_Cursor " + "\r\n";
            queryString = queryString + "               DEALLOCATE Local_Cursor " + "\r\n";

            queryString = queryString + "               SET @lLoop = @lLoop + 1 " + "\r\n";

            queryString = queryString + "               IF @CustomerID > 0 " + "\r\n";
            queryString = queryString + "                   BREAK " + "\r\n";//CustomerID FOUND
            queryString = queryString + "               Else " + "\r\n";
            queryString = queryString + "                   CONTINUE " + "\r\n";

            queryString = queryString + "           END " + "\r\n"; //END LOOP

            queryString = queryString + "   END " + "\r\n";
            //Step 1: FIND CustomerID.END

            //Step 2: @CustomerID FOUND OR @CustomerID <> 0
            queryString = queryString + "   IF @CustomerID > 0 " + "\r\n";
            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           SELECT      TOP 1 CustomerID, Description, DescriptionOfficial " + "\r\n";
            queryString = queryString + "           FROM        ListCustomerName " + "\r\n";
            queryString = queryString + "           WHERE       CustomerID = @CustomerID " + "\r\n";
            queryString = queryString + "       END " + "\r\n";

            SQLDatabase.CreateStoredProcedure("SPGetCustomerName", queryString);
        }



        private static void RestoreSPGetMarketingProgramGroup()
        {
            string queryString = ""; string queryFrom = ""; string queryWhere = "";

            queryFrom = "       DECLARE   Local_Cursor      CURSOR LOCAL FOR     SELECT TOP 1 MarketingProgramGroupID FROM ListMarketingProgramGroup WHERE " + "\r\n";
            queryWhere = "      "; //AND InActive = 0 AND ((@IsCustomer = 1 AND ListMarketingProgramGroup.IsCustomer = 1) OR (@IsSupplier = 1 AND ListMarketingProgramGroup.IsSupplier = 1)) " + "\r\n";

            queryString = "     @MarketingProgramGroupID Int, @MarketingProgramGroupDescription nvarChar (200) " + "\r\n"; //, @IsCustomer Int, @IsSupplier Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";

            //Step 1: FIND MarketingProgramGroupID.BEGIN
            queryString = queryString + "   IF @MarketingProgramGroupID <= 0 AND @MarketingProgramGroupDescription <> '' " + "\r\n"; // FIND @MarketingProgramGroupID BY @MarketingProgramGroup
            queryString = queryString + "   BEGIN " + "\r\n";

            queryString = queryString + "       DECLARE @lLoop Int " + "\r\n"; //BIEN LOOP DE TIM MarketingProgramGroupID
            queryString = queryString + "       SET @lLoop = -1 " + "\r\n";

            queryString = queryString + "       WHILE @lLoop <= 3 " + "\r\n";
            queryString = queryString + "           BEGIN " + "\r\n";
            queryString = queryString + "               IF @lLoop = -1 " + "\r\n";
            queryString = queryString + "                   " + queryFrom + " Description = @MarketingProgramGroupDescription " + queryWhere + "\r\n";
            queryString = queryString + "               ELSE " + "\r\n";
            queryString = queryString + "                   IF @lLoop = 0 " + "\r\n";
            queryString = queryString + "                       " + queryFrom + " Description LIKE @MarketingProgramGroupDescription + '%' " + queryWhere + "\r\n";
            queryString = queryString + "                   ELSE " + "\r\n"; //@lLoop = 3
            queryString = queryString + "                       " + queryFrom + " Description LIKE '%' + @MarketingProgramGroupDescription + '%' " + queryWhere + "\r\n";

            queryString = queryString + "               OPEN Local_Cursor " + "\r\n";

            queryString = queryString + "               FETCH NEXT FROM Local_Cursor INTO @MarketingProgramGroupID " + "\r\n";

            queryString = queryString + "               CLOSE Local_Cursor " + "\r\n";
            queryString = queryString + "               DEALLOCATE Local_Cursor " + "\r\n";

            queryString = queryString + "               SET @lLoop = @lLoop + 1 " + "\r\n";

            queryString = queryString + "               IF @MarketingProgramGroupID > 0 " + "\r\n";
            queryString = queryString + "                   BREAK " + "\r\n";//MarketingProgramGroupID FOUND
            queryString = queryString + "               Else " + "\r\n";
            queryString = queryString + "                   CONTINUE " + "\r\n";

            queryString = queryString + "           END " + "\r\n"; //END LOOP

            queryString = queryString + "   END " + "\r\n";
            //Step 1: FIND MarketingProgramGroupID.END

            //Step 2: @MarketingProgramGroupID FOUND OR @MarketingProgramGroupID <> 0
            queryString = queryString + "   IF @MarketingProgramGroupID > 0 " + "\r\n";
            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           SELECT      TOP 1 MarketingProgramGroupID, Description " + "\r\n";
            queryString = queryString + "           FROM        ListMarketingProgramGroup " + "\r\n";
            queryString = queryString + "           WHERE       MarketingProgramGroupID = @MarketingProgramGroupID " + "\r\n";
            queryString = queryString + "       END " + "\r\n";

            SQLDatabase.CreateStoredProcedure("SPGetMarketingProgramGroup", queryString);
        }



        private static void RestoreSPGetMarketingProgramType()
        {
            string queryString = ""; string queryFrom = ""; string queryWhere = "";

            queryFrom = "       DECLARE   Local_Cursor      CURSOR LOCAL FOR     SELECT TOP 1 MarketingProgramTypeID FROM ListMarketingProgramType WHERE " + "\r\n";
            queryWhere = "      "; //AND InActive = 0 AND ((@IsCustomer = 1 AND ListMarketingProgramType.IsCustomer = 1) OR (@IsSupplier = 1 AND ListMarketingProgramType.IsSupplier = 1)) " + "\r\n";

            queryString = "     @MarketingProgramTypeID Int, @MarketingProgramTypeDescription nvarChar (200) " + "\r\n"; //, @IsCustomer Int, @IsSupplier Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";

            //Step 1: FIND MarketingProgramTypeID.BEGIN
            queryString = queryString + "   IF @MarketingProgramTypeID <= 0 AND @MarketingProgramTypeDescription <> '' " + "\r\n"; // FIND @MarketingProgramTypeID BY @MarketingProgramType
            queryString = queryString + "   BEGIN " + "\r\n";

            queryString = queryString + "       DECLARE @lLoop Int " + "\r\n"; //BIEN LOOP DE TIM MarketingProgramTypeID
            queryString = queryString + "       SET @lLoop = -1 " + "\r\n";

            queryString = queryString + "       WHILE @lLoop <= 3 " + "\r\n";
            queryString = queryString + "           BEGIN " + "\r\n";
            queryString = queryString + "               IF @lLoop = -1 " + "\r\n";
            queryString = queryString + "                   " + queryFrom + " Description = @MarketingProgramTypeDescription " + queryWhere + "\r\n";
            queryString = queryString + "               ELSE " + "\r\n";
            queryString = queryString + "                   IF @lLoop = 0 " + "\r\n";
            queryString = queryString + "                       " + queryFrom + " Description LIKE @MarketingProgramTypeDescription + '%' " + queryWhere + "\r\n";
            queryString = queryString + "                   ELSE " + "\r\n"; //@lLoop = 3
            queryString = queryString + "                       " + queryFrom + " Description LIKE '%' + @MarketingProgramTypeDescription + '%' " + queryWhere + "\r\n";

            queryString = queryString + "               OPEN Local_Cursor " + "\r\n";

            queryString = queryString + "               FETCH NEXT FROM Local_Cursor INTO @MarketingProgramTypeID " + "\r\n";

            queryString = queryString + "               CLOSE Local_Cursor " + "\r\n";
            queryString = queryString + "               DEALLOCATE Local_Cursor " + "\r\n";

            queryString = queryString + "               SET @lLoop = @lLoop + 1 " + "\r\n";

            queryString = queryString + "               IF @MarketingProgramTypeID > 0 " + "\r\n";
            queryString = queryString + "                   BREAK " + "\r\n";//MarketingProgramTypeID FOUND
            queryString = queryString + "               Else " + "\r\n";
            queryString = queryString + "                   CONTINUE " + "\r\n";

            queryString = queryString + "           END " + "\r\n"; //END LOOP

            queryString = queryString + "   END " + "\r\n";
            //Step 1: FIND MarketingProgramTypeID.END

            //Step 2: @MarketingProgramTypeID FOUND OR @MarketingProgramTypeID <> 0
            queryString = queryString + "   IF @MarketingProgramTypeID > 0 " + "\r\n";
            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           SELECT      TOP 1 MarketingProgramTypeID, Description " + "\r\n";
            queryString = queryString + "           FROM        ListMarketingProgramType " + "\r\n";
            queryString = queryString + "           WHERE       MarketingProgramTypeID = @MarketingProgramTypeID " + "\r\n";
            queryString = queryString + "       END " + "\r\n";

            SQLDatabase.CreateStoredProcedure("SPGetMarketingProgramType", queryString);
        }



        private static void RestoreSPGetTimeBase()
        {
            string queryString = ""; string queryFrom = ""; string queryWhere = "";

            queryFrom = "       DECLARE   Local_Cursor      CURSOR LOCAL FOR     SELECT TOP 1 TimeBaseID FROM ListTimeBase WHERE " + "\r\n";
            queryWhere = "      "; //AND InActive = 0 AND ((@IsCustomer = 1 AND ListTimeBase.IsCustomer = 1) OR (@IsSupplier = 1 AND ListTimeBase.IsSupplier = 1)) " + "\r\n";

            queryString = "     @TimeBaseID Int, @TimeBaseDescription nvarChar (200) " + "\r\n"; //, @IsCustomer Int, @IsSupplier Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";

            //Step 1: FIND TimeBaseID.BEGIN
            queryString = queryString + "   IF @TimeBaseID <= 0 AND @TimeBaseDescription <> '' " + "\r\n"; // FIND @TimeBaseID BY @TimeBase
            queryString = queryString + "   BEGIN " + "\r\n";

            queryString = queryString + "       DECLARE @lLoop Int " + "\r\n"; //BIEN LOOP DE TIM TimeBaseID
            queryString = queryString + "       SET @lLoop = -1 " + "\r\n";

            queryString = queryString + "       WHILE @lLoop <= 3 " + "\r\n";
            queryString = queryString + "           BEGIN " + "\r\n";
            queryString = queryString + "               IF @lLoop = -1 " + "\r\n";
            queryString = queryString + "                   " + queryFrom + " Description = @TimeBaseDescription " + queryWhere + "\r\n";
            queryString = queryString + "               ELSE " + "\r\n";
            queryString = queryString + "                   IF @lLoop = 0 " + "\r\n";
            queryString = queryString + "                       " + queryFrom + " Description LIKE @TimeBaseDescription + '%' " + queryWhere + "\r\n";
            queryString = queryString + "                   ELSE " + "\r\n"; //@lLoop = 3
            queryString = queryString + "                       " + queryFrom + " Description LIKE '%' + @TimeBaseDescription + '%' " + queryWhere + "\r\n";

            queryString = queryString + "               OPEN Local_Cursor " + "\r\n";

            queryString = queryString + "               FETCH NEXT FROM Local_Cursor INTO @TimeBaseID " + "\r\n";

            queryString = queryString + "               CLOSE Local_Cursor " + "\r\n";
            queryString = queryString + "               DEALLOCATE Local_Cursor " + "\r\n";

            queryString = queryString + "               SET @lLoop = @lLoop + 1 " + "\r\n";

            queryString = queryString + "               IF @TimeBaseID > 0 " + "\r\n";
            queryString = queryString + "                   BREAK " + "\r\n";//TimeBaseID FOUND
            queryString = queryString + "               Else " + "\r\n";
            queryString = queryString + "                   CONTINUE " + "\r\n";

            queryString = queryString + "           END " + "\r\n"; //END LOOP

            queryString = queryString + "   END " + "\r\n";
            //Step 1: FIND TimeBaseID.END

            //Step 2: @TimeBaseID FOUND OR @TimeBaseID <> 0
            queryString = queryString + "   IF @TimeBaseID > 0 " + "\r\n";
            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           SELECT      TOP 1 TimeBaseID, Description " + "\r\n";
            queryString = queryString + "           FROM        ListTimeBase " + "\r\n";
            queryString = queryString + "           WHERE       TimeBaseID = @TimeBaseID " + "\r\n";
            queryString = queryString + "       END " + "\r\n";

            SQLDatabase.CreateStoredProcedure("SPGetTimeBase", queryString);
        }

        private static void RestoreSPGetMarketingPaymentType()
        {
            string queryString = ""; string queryFrom = ""; string queryWhere = "";

            queryFrom = "       DECLARE   Local_Cursor      CURSOR LOCAL FOR     SELECT TOP 1 MarketingPaymentTypeID FROM ListMarketingPaymentType WHERE " + "\r\n";
            queryWhere = "      "; //AND InActive = 0 AND ((@IsCustomer = 1 AND ListMarketingPaymentType.IsCustomer = 1) OR (@IsSupplier = 1 AND ListMarketingPaymentType.IsSupplier = 1)) " + "\r\n";

            queryString = "     @MarketingPaymentTypeID Int, @MarketingPaymentTypeDescription nvarChar (200) " + "\r\n"; //, @IsCustomer Int, @IsSupplier Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";

            //Step 1: FIND MarketingPaymentTypeID.BEGIN
            queryString = queryString + "   IF @MarketingPaymentTypeID <= 0 AND @MarketingPaymentTypeDescription <> '' " + "\r\n"; // FIND @MarketingPaymentTypeID BY @MarketingPaymentType
            queryString = queryString + "   BEGIN " + "\r\n";

            queryString = queryString + "       DECLARE @lLoop Int " + "\r\n"; //BIEN LOOP DE TIM MarketingPaymentTypeID
            queryString = queryString + "       SET @lLoop = -1 " + "\r\n";

            queryString = queryString + "       WHILE @lLoop <= 3 " + "\r\n";
            queryString = queryString + "           BEGIN " + "\r\n";
            queryString = queryString + "               IF @lLoop = -1 " + "\r\n";
            queryString = queryString + "                   " + queryFrom + " Description = @MarketingPaymentTypeDescription " + queryWhere + "\r\n";
            queryString = queryString + "               ELSE " + "\r\n";
            queryString = queryString + "                   IF @lLoop = 0 " + "\r\n";
            queryString = queryString + "                       " + queryFrom + " Description LIKE @MarketingPaymentTypeDescription + '%' " + queryWhere + "\r\n";
            queryString = queryString + "                   ELSE " + "\r\n"; //@lLoop = 3
            queryString = queryString + "                       " + queryFrom + " Description LIKE '%' + @MarketingPaymentTypeDescription + '%' " + queryWhere + "\r\n";

            queryString = queryString + "               OPEN Local_Cursor " + "\r\n";

            queryString = queryString + "               FETCH NEXT FROM Local_Cursor INTO @MarketingPaymentTypeID " + "\r\n";

            queryString = queryString + "               CLOSE Local_Cursor " + "\r\n";
            queryString = queryString + "               DEALLOCATE Local_Cursor " + "\r\n";

            queryString = queryString + "               SET @lLoop = @lLoop + 1 " + "\r\n";

            queryString = queryString + "               IF @MarketingPaymentTypeID > 0 " + "\r\n";
            queryString = queryString + "                   BREAK " + "\r\n";//MarketingPaymentTypeID FOUND
            queryString = queryString + "               Else " + "\r\n";
            queryString = queryString + "                   CONTINUE " + "\r\n";

            queryString = queryString + "           END " + "\r\n"; //END LOOP

            queryString = queryString + "   END " + "\r\n";
            //Step 1: FIND MarketingPaymentTypeID.END

            //Step 2: @MarketingPaymentTypeID FOUND OR @MarketingPaymentTypeID <> 0
            queryString = queryString + "   IF @MarketingPaymentTypeID > 0 " + "\r\n";
            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           SELECT      TOP 1 MarketingPaymentTypeID, Description " + "\r\n";
            queryString = queryString + "           FROM        ListMarketingPaymentType " + "\r\n";
            queryString = queryString + "           WHERE       MarketingPaymentTypeID = @MarketingPaymentTypeID " + "\r\n";
            queryString = queryString + "       END " + "\r\n";

            SQLDatabase.CreateStoredProcedure("SPGetMarketingPaymentType", queryString);
        }


        private static void RestoreSPGetMarketingPaymentTerm()
        {
            string queryString = ""; string queryFrom = ""; string queryWhere = "";

            queryFrom = "       DECLARE   Local_Cursor      CURSOR LOCAL FOR     SELECT TOP 1 MarketingPaymentTermID FROM ListMarketingPaymentTerm WHERE " + "\r\n";
            queryWhere = "      "; //AND InActive = 0 AND ((@IsCustomer = 1 AND ListMarketingPaymentTerm.IsCustomer = 1) OR (@IsSupplier = 1 AND ListMarketingPaymentTerm.IsSupplier = 1)) " + "\r\n";

            queryString = "     @MarketingPaymentTermID Int, @MarketingPaymentTermDescription nvarChar (200) " + "\r\n"; //, @IsCustomer Int, @IsSupplier Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";

            //Step 1: FIND MarketingPaymentTermID.BEGIN
            queryString = queryString + "   IF @MarketingPaymentTermID <= 0 AND @MarketingPaymentTermDescription <> '' " + "\r\n"; // FIND @MarketingPaymentTermID BY @MarketingPaymentTerm
            queryString = queryString + "   BEGIN " + "\r\n";

            queryString = queryString + "       DECLARE @lLoop Int " + "\r\n"; //BIEN LOOP DE TIM MarketingPaymentTermID
            queryString = queryString + "       SET @lLoop = -1 " + "\r\n";

            queryString = queryString + "       WHILE @lLoop <= 3 " + "\r\n";
            queryString = queryString + "           BEGIN " + "\r\n";
            queryString = queryString + "               IF @lLoop = -1 " + "\r\n";
            queryString = queryString + "                   " + queryFrom + " Description = @MarketingPaymentTermDescription " + queryWhere + "\r\n";
            queryString = queryString + "               ELSE " + "\r\n";
            queryString = queryString + "                   IF @lLoop = 0 " + "\r\n";
            queryString = queryString + "                       " + queryFrom + " Description LIKE @MarketingPaymentTermDescription + '%' " + queryWhere + "\r\n";
            queryString = queryString + "                   ELSE " + "\r\n"; //@lLoop = 3
            queryString = queryString + "                       " + queryFrom + " Description LIKE '%' + @MarketingPaymentTermDescription + '%' " + queryWhere + "\r\n";

            queryString = queryString + "               OPEN Local_Cursor " + "\r\n";

            queryString = queryString + "               FETCH NEXT FROM Local_Cursor INTO @MarketingPaymentTermID " + "\r\n";

            queryString = queryString + "               CLOSE Local_Cursor " + "\r\n";
            queryString = queryString + "               DEALLOCATE Local_Cursor " + "\r\n";

            queryString = queryString + "               SET @lLoop = @lLoop + 1 " + "\r\n";

            queryString = queryString + "               IF @MarketingPaymentTermID > 0 " + "\r\n";
            queryString = queryString + "                   BREAK " + "\r\n";//MarketingPaymentTermID FOUND
            queryString = queryString + "               Else " + "\r\n";
            queryString = queryString + "                   CONTINUE " + "\r\n";

            queryString = queryString + "           END " + "\r\n"; //END LOOP

            queryString = queryString + "   END " + "\r\n";
            //Step 1: FIND MarketingPaymentTermID.END

            //Step 2: @MarketingPaymentTermID FOUND OR @MarketingPaymentTermID <> 0
            queryString = queryString + "   IF @MarketingPaymentTermID > 0 " + "\r\n";
            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           SELECT      TOP 1 MarketingPaymentTermID, Description " + "\r\n";
            queryString = queryString + "           FROM        ListMarketingPaymentTerm " + "\r\n";
            queryString = queryString + "           WHERE       MarketingPaymentTermID = @MarketingPaymentTermID " + "\r\n";
            queryString = queryString + "       END " + "\r\n";

            SQLDatabase.CreateStoredProcedure("SPGetMarketingPaymentTerm", queryString);
        }


        private static void RestoreSPGetStaffName()
        {
            string queryString = ""; string queryFrom = ""; string queryWhere = "";

            queryFrom = "       DECLARE   Local_Cursor      CURSOR LOCAL FOR     SELECT TOP 1 StaffID FROM ListStaffName WHERE " + "\r\n";
            queryWhere = "      "; 

            queryString = "     @StaffID Int, @StaffDescription nvarChar (200) " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";

            //Step 1: FIND StaffID.BEGIN
            queryString = queryString + "   IF @StaffID <= 0 AND @StaffDescription  <> '' " + "\r\n"; // FIND @StaffID BY @StaffDescription 
            queryString = queryString + "   BEGIN " + "\r\n";

            queryString = queryString + "       DECLARE @lLoop Int " + "\r\n"; //BIEN LOOP DE TIM StaffID
            queryString = queryString + "       SET @lLoop = -1 " + "\r\n";

            queryString = queryString + "       WHILE @lLoop <= 5 " + "\r\n";
            queryString = queryString + "           BEGIN " + "\r\n";
            queryString = queryString + "               IF @lLoop = -1 " + "\r\n";
            queryString = queryString + "                   " + queryFrom + " Description + '-' + DescriptionOfficial = @StaffDescription  AND InActive = 0 " + queryWhere + "\r\n";
            queryString = queryString + "               ELSE " + "\r\n";
            queryString = queryString + "                   IF @lLoop = 0 " + "\r\n";
            queryString = queryString + "                       " + queryFrom + " DescriptionOfficial + '-' + Description = @StaffDescription  AND InActive = 0 " + queryWhere + "\r\n";
            queryString = queryString + "                   ELSE " + "\r\n";
            queryString = queryString + "                       IF @lLoop = 1 " + "\r\n";
            queryString = queryString + "                           " + queryFrom + " DescriptionOfficial = @StaffDescription  AND InActive = 0 " + queryWhere + "\r\n";
            queryString = queryString + "                       ELSE " + "\r\n";
            queryString = queryString + "                           IF @lLoop = 2 " + "\r\n";
            queryString = queryString + "                               " + queryFrom + " Description = @StaffDescription  AND InActive = 0 " + queryWhere + "\r\n";
            queryString = queryString + "                           ELSE " + "\r\n";
            queryString = queryString + "                               IF @lLoop = 3 " + "\r\n";
            queryString = queryString + "                                   " + queryFrom + " DescriptionOfficial LIKE '%' + @StaffDescription  + '%' AND InActive = 0 " + queryWhere + "\r\n";
            queryString = queryString + "                               ELSE " + "\r\n";
            queryString = queryString + "                                   IF @lLoop = 4 " + "\r\n";
            queryString = queryString + "                                       " + queryFrom + " Description LIKE @StaffDescription  + '%' AND InActive = 0 " + queryWhere + "\r\n";
            queryString = queryString + "                                   ELSE " + "\r\n"; //@lLoop = 5
            queryString = queryString + "                                       " + queryFrom + " Description LIKE '%' + @StaffDescription  + '%' AND InActive = 0 " + queryWhere + "\r\n";

            queryString = queryString + "               OPEN Local_Cursor " + "\r\n";

            queryString = queryString + "               FETCH NEXT FROM Local_Cursor INTO @StaffID " + "\r\n";

            queryString = queryString + "               CLOSE Local_Cursor " + "\r\n";
            queryString = queryString + "               DEALLOCATE Local_Cursor " + "\r\n";

            queryString = queryString + "               SET @lLoop = @lLoop + 1 " + "\r\n";

            queryString = queryString + "               IF @StaffID > 0 " + "\r\n";
            queryString = queryString + "                   BREAK " + "\r\n";//StaffID FOUND
            queryString = queryString + "               Else " + "\r\n";
            queryString = queryString + "                   CONTINUE " + "\r\n";

            queryString = queryString + "           END " + "\r\n"; //END LOOP

            queryString = queryString + "   END " + "\r\n";
            //Step 1: FIND StaffID.END

            //Step 2: @StaffID FOUND OR @StaffID <> 0
            queryString = queryString + "   IF @StaffID > 0 " + "\r\n";
            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           SELECT      TOP 1 StaffID, Description, DescriptionOfficial " + "\r\n";
            queryString = queryString + "           FROM        ListStaffName " + "\r\n";
            queryString = queryString + "           WHERE       StaffID = @StaffID " + "\r\n";
            queryString = queryString + "       END " + "\r\n";

            SQLDatabase.CreateStoredProcedure("SPGetStaffName", queryString);
        }


        private static void RestoreSPGetEmployeeMaster()
        {
            string queryString = ""; string queryFrom = ""; string queryWhere = "";

            queryFrom = "       DECLARE   Local_Cursor      CURSOR LOCAL FOR     SELECT TOP 1 EmployeeID FROM HRmgrUIP.dbo.EmployeeMaster WHERE " + "\r\n";
            queryWhere = "      "; //AND InActive = 0 AND ((@IsCustomer = 1 AND EmployeeMaster.IsCustomer = 1) OR (@IsSupplier = 1 AND EmployeeMaster.IsSupplier = 1)) " + "\r\n";

            queryString = "     @EmployeeID Int, @EmployeeName nvarChar (200) " + "\r\n"; //, @IsCustomer Int, @IsSupplier Int " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";

            //Step 1: FIND EmployeeID.BEGIN
            queryString = queryString + "   IF @EmployeeID <= 0 AND @EmployeeName <> '' " + "\r\n"; // FIND @EmployeeID BY @EmployeeMaster
            queryString = queryString + "   BEGIN " + "\r\n";

            queryString = queryString + "       DECLARE @lLoop Int " + "\r\n"; //BIEN LOOP DE TIM EmployeeID
            queryString = queryString + "       SET @lLoop = -1 " + "\r\n";

            queryString = queryString + "       WHILE @lLoop <= 3 " + "\r\n";
            queryString = queryString + "           BEGIN " + "\r\n";
            queryString = queryString + "               IF @lLoop = -1 " + "\r\n";
            queryString = queryString + "                   " + queryFrom + " FirstName + ' ' + LastName = @EmployeeName " + queryWhere + "\r\n";
            queryString = queryString + "               ELSE " + "\r\n";
            queryString = queryString + "                   IF @lLoop = 0 " + "\r\n";
            queryString = queryString + "                       " + queryFrom + " FirstName + ' ' + LastName LIKE @EmployeeName + '%' " + queryWhere + "\r\n";
            queryString = queryString + "                   ELSE " + "\r\n"; //@lLoop = 3
            queryString = queryString + "                       " + queryFrom + " FirstName + ' ' + LastName LIKE '%' + @EmployeeName + '%' " + queryWhere + "\r\n";

            queryString = queryString + "               OPEN Local_Cursor " + "\r\n";

            queryString = queryString + "               FETCH NEXT FROM Local_Cursor INTO @EmployeeID " + "\r\n";

            queryString = queryString + "               CLOSE Local_Cursor " + "\r\n";
            queryString = queryString + "               DEALLOCATE Local_Cursor " + "\r\n";

            queryString = queryString + "               SET @lLoop = @lLoop + 1 " + "\r\n";

            queryString = queryString + "               IF @EmployeeID > 0 " + "\r\n";
            queryString = queryString + "                   BREAK " + "\r\n";//EmployeeID FOUND
            queryString = queryString + "               Else " + "\r\n";
            queryString = queryString + "                   CONTINUE " + "\r\n";

            queryString = queryString + "           END " + "\r\n"; //END LOOP

            queryString = queryString + "   END " + "\r\n";
            //Step 1: FIND EmployeeID.END

            //Step 2: @EmployeeID FOUND OR @EmployeeID <> 0
            queryString = queryString + "   IF @EmployeeID > 0 " + "\r\n";
            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           SELECT      TOP 1 EmployeeID, FirstName + ' ' + LastName AS EmployeeName" + "\r\n";
            queryString = queryString + "           FROM        HRmgrUIP.dbo.EmployeeMaster " + "\r\n";
            queryString = queryString + "           WHERE       EmployeeID = @EmployeeID " + "\r\n";
            queryString = queryString + "       END " + "\r\n";

            SQLDatabase.CreateStoredProcedure("SPGetEmployeeMaster", queryString);
        }



        private static void RestoreSPGetMarketingProgram()
        {
            string queryString = ""; string queryFrom = ""; string queryWhere = "";

            queryFrom = "       DECLARE   Local_Cursor      CURSOR LOCAL FOR     SELECT TOP 1 MarketingProgramID FROM MarketingProgramMaster WHERE " + "\r\n";
            queryWhere = "      "; //AND InActive = 0 " + "\r\n";

            queryString = "     @MarketingProgramID Int, @Reference nvarChar (200) " + "\r\n"; 
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";

            //Step 1: FIND MarketingProgramID.BEGIN
            queryString = queryString + "   IF @MarketingProgramID <= 0 AND @Reference <> '' " + "\r\n"; // FIND @MarketingProgramID BY @MarketingProgram
            queryString = queryString + "   BEGIN " + "\r\n";

            queryString = queryString + "       DECLARE @lLoop Int " + "\r\n"; //BIEN LOOP DE TIM MarketingProgramID
            queryString = queryString + "       SET @lLoop = -1 " + "\r\n";

            queryString = queryString + "       WHILE @lLoop <= 3 " + "\r\n";
            queryString = queryString + "           BEGIN " + "\r\n";
            queryString = queryString + "               IF @lLoop = -1 " + "\r\n";
            queryString = queryString + "                   " + queryFrom + " Reference = @Reference " + queryWhere + "\r\n";
            queryString = queryString + "               ELSE " + "\r\n";
            queryString = queryString + "                   IF @lLoop = 0 " + "\r\n";
            queryString = queryString + "                       " + queryFrom + " Reference LIKE @Reference + '%' " + queryWhere + "\r\n";
            queryString = queryString + "                   ELSE " + "\r\n"; //@lLoop = 3
            queryString = queryString + "                       " + queryFrom + " Reference LIKE '%' + @Reference + '%' " + queryWhere + "\r\n";

            queryString = queryString + "               OPEN Local_Cursor " + "\r\n";

            queryString = queryString + "               FETCH NEXT FROM Local_Cursor INTO @MarketingProgramID " + "\r\n";

            queryString = queryString + "               CLOSE Local_Cursor " + "\r\n";
            queryString = queryString + "               DEALLOCATE Local_Cursor " + "\r\n";

            queryString = queryString + "               SET @lLoop = @lLoop + 1 " + "\r\n";

            queryString = queryString + "               IF @MarketingProgramID > 0 " + "\r\n";
            queryString = queryString + "                   BREAK " + "\r\n";//MarketingProgramID FOUND
            queryString = queryString + "               Else " + "\r\n";
            queryString = queryString + "                   CONTINUE " + "\r\n";

            queryString = queryString + "           END " + "\r\n"; //END LOOP

            queryString = queryString + "   END " + "\r\n";
            //Step 1: FIND MarketingProgramID.END

            //Step 2: @MarketingProgramID FOUND OR @MarketingProgramID <> 0
            queryString = queryString + "   IF @MarketingProgramID > 0 " + "\r\n";
            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           SELECT      TOP 1 MarketingProgramID, Reference " + "\r\n";
            queryString = queryString + "           FROM        MarketingProgramMaster " + "\r\n";
            queryString = queryString + "           WHERE       MarketingProgramID = @MarketingProgramID " + "\r\n";
            queryString = queryString + "       END " + "\r\n";

            SQLDatabase.CreateStoredProcedure("SPGetMarketingProgram", queryString);
        }


        private static void RestoreSPGetSalesmenName()
        {
            string queryString = ""; string queryFrom = ""; string queryWhere = "";

            queryFrom = "       DECLARE   Local_Cursor      CURSOR LOCAL FOR     SELECT TOP 1 SalesmenID FROM ListSalesmenName WHERE " + "\r\n";
            queryWhere = "      "; //AND InActive = 0 " + "\r\n";

            queryString = "     @SalesmenID Int, @Description nvarChar (200) " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";

            //Step 1: FIND SalesmenID.BEGIN
            queryString = queryString + "   IF @SalesmenID <= 0 AND @Description <> '' " + "\r\n"; // FIND @SalesmenID BY @SalesmenName
            queryString = queryString + "   BEGIN " + "\r\n";

            queryString = queryString + "       DECLARE @lLoop Int " + "\r\n"; //BIEN LOOP DE TIM SalesmenID
            queryString = queryString + "       SET @lLoop = -1 " + "\r\n";

            queryString = queryString + "       WHILE @lLoop <= 3 " + "\r\n";
            queryString = queryString + "           BEGIN " + "\r\n";
            queryString = queryString + "               IF @lLoop = -1 " + "\r\n";
            queryString = queryString + "                   " + queryFrom + " Description = @Description " + queryWhere + "\r\n";
            queryString = queryString + "               ELSE " + "\r\n";
            queryString = queryString + "                   IF @lLoop = 0 " + "\r\n";
            queryString = queryString + "                       " + queryFrom + " Description LIKE @Description + '%' " + queryWhere + "\r\n";
            queryString = queryString + "                   ELSE " + "\r\n"; //@lLoop = 3
            queryString = queryString + "                       " + queryFrom + " Description LIKE '%' + @Description + '%' " + queryWhere + "\r\n";

            queryString = queryString + "               OPEN Local_Cursor " + "\r\n";

            queryString = queryString + "               FETCH NEXT FROM Local_Cursor INTO @SalesmenID " + "\r\n";

            queryString = queryString + "               CLOSE Local_Cursor " + "\r\n";
            queryString = queryString + "               DEALLOCATE Local_Cursor " + "\r\n";

            queryString = queryString + "               SET @lLoop = @lLoop + 1 " + "\r\n";

            queryString = queryString + "               IF @SalesmenID > 0 " + "\r\n";
            queryString = queryString + "                   BREAK " + "\r\n";//MarketingProgramID FOUND
            queryString = queryString + "               Else " + "\r\n";
            queryString = queryString + "                   CONTINUE " + "\r\n";

            queryString = queryString + "           END " + "\r\n"; //END LOOP

            queryString = queryString + "   END " + "\r\n";
            //Step 1: FIND SalesmenID.END

            //Step 2: @SalesmenID FOUND OR @SalesmenID <> 0
            queryString = queryString + "   IF @SalesmenID > 0 " + "\r\n";
            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "           SELECT      TOP 1 SalesmenID, Description " + "\r\n";
            queryString = queryString + "           FROM        ListSalesmenName " + "\r\n";
            queryString = queryString + "           WHERE       SalesmenID = @SalesmenID " + "\r\n";
            queryString = queryString + "       END " + "\r\n";

            SQLDatabase.CreateStoredProcedure("SPGetSalesmenName", queryString);
        }


        #endregion Restore Procedure




    }
}
